# _*_ coding utf-8 _*_
# george
# time: 2025/1/4下午6:45
# name: db.py
# comment:
# 创建数据库连接
import pymysql


class MyORM:
    def __init__(self):
        self.db = pymysql.connect(
            host="127.0.0.1",
            password="123",
            port=3306,
            user="root",
            database="flask_test",
        )
        # 创建cursor
        self.cursor = self.db.cursor(pymysql.cursors.DictCursor)

    def execute(self, sql, params=None):
        # 执行sql语句
        try:
            print(sql, params)
            self.cursor.execute(sql, params)
            # 提交到数据库执行
            self.db.commit()
            result = self.cursor.fetchall()
            self.cursor.close()
            self.db.close()
            return result
        except Exception as e:
            self.db.rollback()
            self.cursor.close()
            self.db.close()
            return e


class Model:
    # 插入数据的时候，需要动态设置属性
    def __init__(self, **kwargs):
        # 子类的类名
        self.table_name = self.__class__.__getattribute__(self, "table_name")
        for k, v in kwargs.items():
            # 动态的设置了实例对象的属性
            self.__setattr__(k, v)

    # 使用链式查询列的状况
    def field(self, select_params):
        if select_params and type(select_params) is list:
            self.columns = ",".join(select_params)
            return self
        return self

    def query_one(self, **where_params):
        # 参数1
        if hasattr(self, "columns"):
            sql = f"select {self.columns} from {self.table_name} "
        else:
            sql = f"select * from {self.table_name} "
        # 参数2
        # select job_n,project from users where id=1 and username="root";
        conditions = []
        placeholders = []
        if where_params:
            for k, v in where_params.items():
                conditions.append(f"{k}=%s")
                placeholders.append(v)
            param = "WHERE " + " AND ".join(conditions)
            sql += param
        sql = f"{sql} limit 1"
        return MyORM().execute(sql, placeholders)

    def query_all(self):
        sql = f"select * from {self.table_name}"
        return MyORM().execute(sql, ())

    # 列名不能作为参数传递，你需要以安全的方式构建列名部分
    def insert(self):
        self.__dict__.pop("table_name")
        keys = ', '.join(self.__dict__.keys())
        placeholders = ', '.join(['%s'] * len(self.__dict__))
        sql = f"INSERT INTO {self.table_name} ({keys}) VALUES ({placeholders})"
        # print(sql)
        return MyORM().execute(sql, list(self.__dict__.values()))


class User(Model):
    table_name = "users"



if __name__ == '__main__':
    user = User(job_n="F7692286", username="zs", passwd=555, mobile="1111", project="Cell", role=1)
    # data = user.query_one()
    # user = User()
    # data = user.field(["job_n", "username","passwd"]).query_one(id=2)
    # data = user.query_all()
    # print(data)
    data = user.insert()
    print(data)
    # user =User()
    # print(user.field([]))
    # data = user.query_all()
    # print(data)
    # r = user.query_all()
    # print(r)
